
USE [TestDB1]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Gets all records from the TstMessage table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_Get_List

AS


				
				SELECT
					[MessageID],
					[Message],
					[CreatedDateTime]
				FROM
					[dbo].[TstMessage]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Gets records from the TstMessage table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[MessageID]'
				END

				-- SQL Server 2005 Paging
				DECLARE @SQL AS nvarchar(MAX)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [MessageID]'
				SET @SQL = @SQL + ', [Message]'
				SET @SQL = @SQL + ', [CreatedDateTime]'
				SET @SQL = @SQL + ' FROM [dbo].[TstMessage]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [MessageID],'
				SET @SQL = @SQL + ' [Message],'
				SET @SQL = @SQL + ' [CreatedDateTime]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				EXEC sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[TstMessage]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Inserts a record into the TstMessage table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_Insert
(

	@MessageID int    OUTPUT,

	@Message varchar (50)  ,

	@CreatedDateTime datetime   
)
AS


				
				INSERT INTO [dbo].[TstMessage]
					(
					[Message]
					,[CreatedDateTime]
					)
				VALUES
					(
					@Message
					,@CreatedDateTime
					)
				
				-- Get the identity value
				SET @MessageID = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Updates a record in the TstMessage table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_Update
(

	@MessageID int   ,

	@Message varchar (50)  ,

	@CreatedDateTime datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[TstMessage]
				SET
					[Message] = @Message
					,[CreatedDateTime] = @CreatedDateTime
				WHERE
[MessageID] = @MessageID 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Deletes a record in the TstMessage table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_Delete
(

	@MessageID int   
)
AS


				DELETE FROM [dbo].[TstMessage] WITH (ROWLOCK) 
				WHERE
					[MessageID] = @MessageID
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_GetByMessageID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_GetByMessageID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_GetByMessageID
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Select records from the TstMessage table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_GetByMessageID
(

	@MessageID int   
)
AS


				SELECT
					[MessageID],
					[Message],
					[CreatedDateTime]
				FROM
					[dbo].[TstMessage]
				WHERE
					[MessageID] = @MessageID
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.TstMessage_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.TstMessage_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.TstMessage_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: My Company (www.mycompany.com)
-- Purpose: Finds records in the TstMessage table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.TstMessage_Find
(

	@SearchUsingOR bit   = null ,

	@MessageID int   = null ,

	@Message varchar (50)  = null ,

	@CreatedDateTime datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [MessageID]
	, [Message]
	, [CreatedDateTime]
    FROM
	[dbo].[TstMessage]
    WHERE 
	 ([MessageID] = @MessageID OR @MessageID IS NULL)
	AND ([Message] = @Message OR @Message IS NULL)
	AND ([CreatedDateTime] = @CreatedDateTime OR @CreatedDateTime IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [MessageID]
	, [Message]
	, [CreatedDateTime]
    FROM
	[dbo].[TstMessage]
    WHERE 
	 ([MessageID] = @MessageID AND @MessageID is not null)
	OR ([Message] = @Message AND @Message is not null)
	OR ([CreatedDateTime] = @CreatedDateTime AND @CreatedDateTime is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

